import efinance as ef
import pandas as pd
from sqlalchemy import create_engine
import pymysql


df = ef.stock.get_realtime_quotes()

columns_to_keep = ['股票代码',
 '股票名称',
 '涨跌幅',
 '最新价',
 '量比',
 '成交额',
 '总市值',
 '最新交易日']
df = df[columns_to_keep]



df['ID'] = df['股票代码'].astype(str)  + '-' +  df['最新交易日']

cols = df.columns.tolist()
cols.insert(0, cols.pop(cols.index('ID')))
df = df[cols]

pymysql.install_as_MySQLdb()
engine_ts = create_engine('mysql://root:xxx@127.0.0.1:3306/astock')
df = df.replace('-', 0)

df.to_sql('daily', engine_ts, index=False, if_exists='append', chunksize=5000)
